package com.eastcom.collection.tools.db
import java.sql.ResultSet
import java.sql.Types;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import com.eastcom.collection.provider.DynamicBeanRegisterProvider;
class PmDatabaseOptUtil {
	
	public static void main(String[] args) {
		ApplicationContext context = new FileSystemXmlApplicationContext("config/server_base/dao_config.xml")
		DbEngine dbEngine = new DbEngine()
		JdbcUtil jdbcUtil = new JdbcUtil()
		jdbcUtil.setDataSource(context.getBean("oracle_39_nhm"))
		dbEngine.setJdbcUtil(jdbcUtil)
		String tableName = "SGSN_PM_V3_SGSNFUNCTION"
		dbEngine.makeSql(null, tableName)
		List<String> feildList = new ArrayList<String>()
		List<Integer> typeList = new ArrayList<String>()
//		START_TIME, END_TIME,
		String feilds = "SM_SUCCUPDPDPCONTEXTSGSN, SM_SUCCUPDPDPCONTEXTGGSN, SM_SUCCMODPDPCONTEXTSGSN_RAI, SM_SUCCMODPDPCONTEXTSGSN, SM_SUCCMODPDPCONTEXTMS_RAI, SM_SUCCMODPDPCONTEXTMS, SM_SUCCDEACTPDPCONTEXTSGSN, SM_SUCCDEACTPDPCONTEXTMS, SM_SUCCDEACTPDPCONTEXTGGSN, SM_SUCCACTSECONDPDPCONTEXT_RAI, SM_SUCCACTSECONDPDPCONTEXT, SM_SUCCACTPDPCONTEXTTIMEMODU0, SM_SUCCACTPDPCONTEXTTIMEMODUR, SM_SUCCACTPDPCONTEXTSTATIC, SM_SUCCACTPDPCONTEXT_RAI, SM_SUCCACTPDPCONTEXT, SM_MEANACTPDPCONTEXT, SM_MAXACTPDPCONTEXT, SM_FAILACTPDPCTXTMSUSERCAUSE1, SM_FAILACTPDPCTXTMSUSERCAUSE2, SM_FAILACTPDPCTXTMSUSERCAUSE3, SM_FAILACTPDPCTXTMSUSERCAUSE4, SM_FAILACTPDPCTXTMSUSERCAUSE5, SM_FAILACTPDPCTXTMSUSERCAUSE, SM_ATTUPDPDPCONTEXTSGSN, SM_ATTUPDPDPCONTEXTGGSN, SM_ATTMODPDPCONTEXTSGSN_RAI, SM_ATTMODPDPCONTEXTSGSN, SM_ATTMODPDPCONTEXTMS_RAI, SM_ATTMODPDPCONTEXTMS, SM_ATTDEACTPDPCONTEXTSGSN, SM_ATTDEACTPDPCONTEXTMS, SM_ATTDEACTPDPCONTEXTGGSN, SM_ATTACTSECONDPDPCONTEXT_RAI, SM_ATTACTSECONDPDPCONTEXT, SM_ATTACTPDPCONTEXTSTATIC, SM_ATTACTPDPCONTEXT_RAI, SM_ATTACTPDPCONTEXT, SEC_SUCCSECMODE, SEC_SUCCIDENTITYREQUEST, SEC_SUCCAUTHPROCSSGSN, SEC_ATTSECMODE, SEC_ATTIDENTITYREQUEST, SEC_ATTAUTHPROCSSGSN, RELOC_SUCCINTRASGSN, RELOC_SUCCINTERSGSN, RELOC_ATTINTRASGSN, RELOC_ATTINTERSGSN, MM_SUCCPSPAGINGPROCIU_RAI, MM_SUCCPSPAGINGPROCIU, MM_SUCCPSPAGINGPROCGB_RAI, MM_SUCCPSPAGINGPROCGB, MM_SUCCINTRASGSNRAUPDATE_RAI, MM_SUCCINTRASGSNRAUPDATE, MM_SUCCINTRASGSNPERIODICRAUP6, MM_SUCCINTRASGSNPERIODICRAUP7, MM_SUCCINTERSGSNRAUPDATE_RAI, MM_SUCCINTERSGSNRAUPDATE, MM_SUCCGPRSDETACHSGSN_RAI, MM_SUCCGPRSDETACHSGSN, MM_SUCCGPRSATTACH_RAI, MM_SUCCGPRSATTACH, MM_MEANNBRATTACHEDSUBUSIM_RAI, MM_MEANNBRATTACHEDSUBUSIM, MM_MEANNBRATTACHEDSUBSIM_RAI, MM_MEANNBRATTACHEDSUBSIM, MM_MEANNBRATTACHEDSUB_RAI, MM_MEANNBRATTACHEDSUB, MM_MAXNBRATTACHEDSUB_RAI, MM_MAXNBRATTACHEDSUB, MM_FAILEDGPRSATTACHUSER_8, MM_FAILEDGPRSATTACHUSER_7, MM_FAILEDGPRSATTACHUSER_6, MM_FAILEDGPRSATTACHUSER_3, MM_FAILEDGPRSATTACHUSER, MM_FAILINTRASGSNRAUPDATE_CAUSE, MM_FAILINTRASGSNRAUPDATE, MM_FAILINTERSGSNRAUPDATE_CAUSE, MM_FAILINTERSGSNRAUPDATE, MM_DURATTACH_RAI, MM_DURATTACH, MM_ATTPSPAGINGPROCIU_RAI, MM_ATTPSPAGINGPROCIU, MM_ATTPSPAGINGPROCGB_RAI, MM_ATTPSPAGINGPROCGB, MM_ATTINTRASGSNRAUPDATE_RAI, MM_ATTINTRASGSNRAUPDATE, MM_ATTINTRASGSNPERIODICRAUPD8, MM_ATTINTRASGSNPERIODICRAUPD9, MM_ATTINTERSGSNRAUPDATE_RAI, MM_ATTINTERSGSNRAUPDATE, MM_ATTGPRSDETACHSGSN_RAI, MM_ATTGPRSDETACHSGSN, MM_ATTGPRSDETACHMS_RAI, MM_ATTGPRSDETACHMS, MM_ATTGPRSDETACHHLR_RAI, MM_ATTGPRSDETACHHLR, MM_ATTGPRSATTACH_RAI, MM_ATTGPRSATTACH, MAP_SUCCSENDAUTHINFOHLR, MAP_ATTSENDAUTHINFOHLR, ISYSC_SUCCINTRASGSNUMTSGSMRAU, ISYSC_SUCCINTRASGSNGSMUMTSRAU, ISYSC_ATTINTRASGSNUMTSGSMRAU, ISYSC_ATTINTRASGSNGSMUMTSRAU, GTPP_SUCCCDRTRANSF, GTPP_CDRTRANSFREQ_2, GTPP_CDRTRANSFREQ_1, GTPP_CDRTRANSFREQ, GTP_PEAKOUTRATEGN, GTP_PEAKOUTPKTGN, GTP_PEAKINRATEGN, GTP_PEAKINPKTGN, GTP_OUTPKTGN, GTP_OUTOCTGN, GTP_INPKTGN, GTP_INOCTGN, GTP_DISCPKTGN, GBNS_PEAKOUTRATE_NSEI, GBNS_PEAKOUTRATE, GBNS_PEAKINRATE_NSEI, GBNS_PEAKINRATE, GBNS_OUTOCT_NSEI, GBNS_OUTOCT, GBNS_INOCT_NSEI, GBNS_INOCT, DNS_SUCCDNSRESP, DNS_ATTDNSQUERY"
		
		String[] feildArr = feilds.split("\\s*,\\s*")
		
		feildArr.each {
			feildList.add(it)
			typeList.add(Types.VARCHAR)
		}
		feildList.add("START_TIME")
		feildList.add("END_TIME")
		typeList.add(Types.DATE)
		typeList.add(Types.DATE)
		//		Object[] param = new Object[1]
//		param[0] = args[0]
//		int[] type = new int[1]
//		type[0] = Types.VARCHAR
//		ResultSet rs = dbEngine.getJdbcUtil().queryPageAbsolute(sql, param, type, 1, 500)
//		if(rs == null){
//			println "query error!"
//			return
//		}
//		rs.absolute(1)
//		List<String> pmNameList = new ArrayList<String>()
//		List<Integer> typeList = new ArrayList<String>()
//		List<Integer> feildList = new ArrayList<String>()
//		String pmName = rs.getString("PM_NAME")
//		pmNameList.add(pmName)
//		while(rs.next()){
//			pmName = rs.getString("PM_NAME")
//			pmNameList.add(pmName)
//		}
//		rs.absolute(1)
//		String tbTemp = rs.getString("INFOMODELREFERENCED").substring(0, 10)+ "_"+rs.getString("OBJECTTYPE")
//		String tableName = tbTemp.replaceAll("-", "_")
//		int temp = 0
//		Map<String,String> feildMap = new HashMap()
//		pmNameList.each {
//			println it
//			String fd = it.replaceAll("_", "")
//			fd = fd.replaceAll("\\.", "_")
//			if(fd.length()>30){
//				fd = fd.substring(0,28)+temp++
//				feildMap.put(fd, it.value)
//			}
//			feildList.add(fd)
//			typeList.add(Types.VARCHAR)
//		}
//		println pmNameList.size()
//		PmDatabaseOptUtil.createDomainTable(dbEngine, feildList, typeList, tableName)
//		PmDatabaseOptUtil.commentDomainTable(dbEngine, feildMap, tableName)
	}
	
	public static boolean createDomainTable(DbEngine dbEngine, List<String> feilds,List<Integer> types,String tableName){
		String sql = "create table "+tableName+" ("
		sql+="\tSTART_TIME DATE,\n"
		sql+="\tEND_TIME DATE,\n"
		for(int i = 0; i < feilds.size(); i ++){
			sql+="\t"
			sql+=feilds[i]
			sql+="\t"
			switch(types[i]){
				case Types.INTEGER:
				case Types.FLOAT:
				case Types.DOUBLE:
				case Types.DECIMAL:
				case Types.NUMERIC:
					sql+="NUMBER"
					break
				case Types.TIMESTAMP:
					
				case Types.DATE:
					sql+="DATE"
					break
				case Types.VARCHAR:
					sql+="VARCHAR2(512)"
				default:
					break;
			}
			if(i < feilds.size()-1){
				sql+=",\n"
			}
		}
		sql+=")"
		println sql
		dbEngine.getJdbcUtil().execute(sql)
	}
	
	public static boolean commentDomainTable(DbEngine dbEngine, Map<String, String> feildMap,String tableName){
		for(int i = 0; i < feildMap.keySet().size(); i ++){
			String sql = "comment on column "+tableName+"."
			sql += feildMap.keySet()[i]
			sql += " is '"
			sql += feildMap.get(feildMap.keySet()[i])
			sql +="'"
			println sql
			dbEngine.getJdbcUtil().execute(sql)
		}
	}
}
